﻿using DBUtil;
using Function;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAL
{
    public class JsonDAL
    {
        DbHelperSQLP DbHelperSQL = new DbHelperSQLP(PubConstant.JHShop);
        /// <summary>
        /// 根据门店ID获取该门店同比和环比销售额
        /// </summary>
        /// <param name="shopid">门店ID</param>
        /// <returns></returns>
        public DataSet GetOrderMoney(string shopid)
        {
            string sql = string.Format(@"select a.shopid shopid,convert(varchar(7),a.StatDate,120) as month,
sum(a.AllOrderMoney)allmoney,
(select isnull(sum(b.AllOrderMoney),0) from TJ_SellInfoByDay b where a.shopid=b.shopid and StatDate>=dateadd(month,-12,convert(varchar(7),a.StatDate,120)+'-01') and StatDate<dateadd(day,-1,dateadd(month,-11,convert(varchar(7),a.StatDate,120)+'-01')))tbmoney,
(select isnull(sum(b.AllOrderMoney),0) from TJ_SellInfoByDay b where a.shopid=b.shopid and StatDate>=dateadd(month,-1,convert(varchar(7),a.StatDate,120)+'-01') and StatDate<dateadd(day,-1,convert(varchar(7),a.StatDate,120)+'-01'))hbmoney
from TJ_SellInfoByDay a where a.StatDate>convert(varchar(4),getdate(),120)+'-01' and a.shopid={0} group by a.shopid, convert(varchar(7),a.StatDate,120)", shopid);
            return DbHelperSQL.Query(sql.ToString());
        }
        /// <summary>
        /// 根据门店ID获取该门店同比和环比
        /// </summary>
        /// <param name="shopid">门店ID</param>
        /// <returns></returns>
        public DataSet GetNum(string shopid)
        {
            string sql = string.Format(@"select a.shopid shopid,convert(varchar(7),a.StatDate,120) as month,
sum(a.SellNum+a.LargeNum+a.SendNum+a.TempOrderNum)allNum,
(select isnull(sum(b.SellNum+b.LargeNum+b.SendNum+b.TempOrderNum),0) from TJ_SellInfoByDay b where a.shopid=b.shopid and StatDate>=dateadd(month,-12,convert(varchar(7),a.StatDate,120)+'-01') and StatDate<dateadd(day,-1,dateadd(month,-11,convert(varchar(7),a.StatDate,120)+'-01')))tbNum,
(select isnull(sum(b.SellNum+b.LargeNum+b.SendNum+b.TempOrderNum),0) from TJ_SellInfoByDay b where a.shopid=b.shopid and StatDate>=dateadd(month,-1,convert(varchar(7),a.StatDate,120)+'-01') and StatDate<dateadd(day,-1,convert(varchar(7),a.StatDate,120)+'-01'))hbNum
from TJ_SellInfoByDay a where a.StatDate>convert(varchar(4),getdate(),120)+'-01' and a.shopid={0} group by a.shopid, convert(varchar(7),a.StatDate,120)", shopid);
            return DbHelperSQL.Query(sql.ToString());
        }
        /// <summary>
        /// 根据门店ID和时间查询销售占比情况
        /// </summary>
        /// <param name="shopid">门店ID</param>
        /// <param name="date">时间</param>
        /// <returns></returns>
        public DataSet GetOrderZb(string shopid, string date)
        {
            string sql = string.Format("select * from TJ_SellGoodsTypeByMonth where shopID = {0} and statMonth=convert(varchar(7),'{1}',120)", shopid, date);
            return DbHelperSQL.Query(sql.ToString());
        }
        /// <summary>
        /// 统计查询
        /// </summary>
        /// <param name="_pageCount">数据条数</param>
        /// <param name="dateType">查询类型  1为每天 2为每月 3为每年</param>
        /// <param name="shopid">门店ID</param>
        /// <param name="pagesize">页长</param>
        /// <param name="pageindex">当前页</param>
        /// <param name="_connection">连接字符串</param>
        /// <param name="_strMess">返回消息</param>
        /// <returns></returns>
        public static DataTable SelectPage(out int _pageCount, int dateType, int shopid, int pagesize, int pageindex, string _connection, out string _strMess)
        {
            _pageCount = 0;
            DataTable _myTBl = new DataTable();
            try
            {
                SqlParameter[] sqlparams = {
                new SqlParameter("@dateType",SqlDbType.Int),
                new SqlParameter("@shopid",SqlDbType.Int),
                new SqlParameter("@pagesize",SqlDbType.Int),               
                new SqlParameter("@pageindex",SqlDbType.Int),     
                new SqlParameter("@Counts",SqlDbType.Int)
                };
                sqlparams[0].Value = dateType;
                sqlparams[1].Value = shopid;
                sqlparams[2].Value = pagesize;
                sqlparams[3].Value = pageindex;
                sqlparams[4].Direction = ParameterDirection.Output;

                Sql_BaseDAL MySqlBase = new Sql_BaseDAL();
            MySqlBase.myParameter = sqlparams;
            MySqlBase.SqlConnectionString = _connection;
                _myTBl = MySqlBase.SqlProcedure_DataTable(out _strMess, "proc_SellInfo_View", true, out _pageCount);
            }
            catch (ArgumentException e)
            {
                _strMess = e.Message;
            }
            return _myTBl;
        }
    }
}
